package com.czxy.changgou3.com.czxy.changgou3.mapper;

import com.czxy.changgou3.pojo.SkuComment;
import org.apache.ibatis.annotations.*;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

/**
 * @author zhangmengxiang1@itcast.cn
 * @version 1.0
 * @date 2020/4/23
 */
@org.apache.ibatis.annotations.Mapper
public interface SkuCommentMapper extends Mapper<SkuComment> {

    /**
     * 查询评论数
     * @param spuId
     * @return
     */
    @Select("select count(*) from tb_sku_comment where spu_id = #{spuId}")
    public Integer findNumBySpuId(@Param("spuId") Integer spuId);

    /**
     * 查询评论数
     * @param skuId
     * @return
     */
    @Select("select count(*) from tb_sku_comment where spu_id = #{skuId}")
    public Integer findNumBySkuId(@Param("skuId") Integer skuId);

    /**
     * 根据sku_id 查询评论平均数
     * @param skuId
     * @return
     */
    @Select("SELECT AVG(star) FROM tb_sku_comment WHERE sku_id = #{skuId}")
    public Double findAvgStarBySkuId(@Param("skuId") Integer skuId);


    /**
     * 指定spu前提下，根据ratio查询评论数
     * @param spuId
     * @param ratio
     * @return
     */
    @Select("select count(*) from tb_sku_comment where spu_id = #{spuId} and ratio = #{ratio}")
    public Integer findCommentCountByRatio(@Param("spuId") Integer spuId, @Param("ratio") String ratio );


    /**
     * 查找指定spu所有的评论
     * @param spuId
     * @return
     */
    @Select("select * from tb_sku_comment where spu_id = #{spuId}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "updatedAt", column = "updated_at"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "spuId", column = "spu_id"),
            @Result(property = "skuId", column = "sku_id"),
            @Result(property = "ratio", column = "ratio"),
            @Result(property = "specList", column = "spec_list"),
            @Result(property = "content", column = "content"),
            @Result(property = "star", column = "star"),
            @Result(property = "isShow", column = "isshow"),
            @Result(property = "sn", column = "sn"),
            @Result(property = "user", one = @One(select="com.czxy.changgou3.com.czxy.changgou3.mapper.UserMapper.selectByPrimaryKey"), column = "user_id"),
    })
    public List<SkuComment> findCommentsBySpuid(@Param("spuId") Integer spuId);
}
